package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.util.RequestUtils;
import com.bringway.p2p.dao.QxtzjlDao;
import com.bringway.p2p.dao.ZqzrsqDao;

import net.sf.json.JSONObject;
@Repository
public class QxtzjlDaoImpl extends P2PJdbcDao implements QxtzjlDao{
	Logger logger=Logger.getLogger(QxtzjlDaoImpl.class);

	@Override
	public List<Map<String, Object>> queryQxtzjl(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String username = params.optString("username");
		String htbh = params.optString("htbh");
		String tzxm = params.optString("tzxm");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append("SELECT REF_TZRXX_ID,USERID,ZQID,TZJE ,date_format(TZSJ,'%Y-%m-%d') TZSJ,TCZT,HKZT,JXRQ,TZSY,HTBH,LY_TYPE,SXF_BL,SXF");
		sql.append("  FROM t_zhfw_cz_tzrxx");
		sql.append("   WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERID like "+ "'%"+username+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND HTBH like "+ "'%"+htbh+"%'");
		}
		if(StringUtils.isNotEmpty(tzxm)){
			sql.append(" AND LY_TYPE =:tzxm");
			paramMap.put("tzxm", tzxm);
		}
		sql.append(" order by ID desc LIMIT :START,:LIMIT");
		logger.info("queryQxtzjl sql is "+sql.toString());
		logger.info("queryQxtzjl paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getQxtzjlCount(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String username = params.optString("username");
		String htbh = params.optString("htbh");
		String tzxm = params.optString("tzxm");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append("SELECT count(*)");
		sql.append("  FROM t_zhfw_cz_tzrxx");
		sql.append("   WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERID like "+ "'%"+username+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND HTBH like "+ "'%"+htbh+"%'");
		}
		if(StringUtils.isNotEmpty(tzxm)){
			sql.append(" AND LY_TYPE =:tzxm");
			paramMap.put("tzxm", tzxm);
		}
		logger.info("getQxtzjlCount sql is "+sql.toString());
		logger.info("getQxtzjlCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@Override
	public String gettzjeSum(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String username = params.optString("username");
		String htbh = params.optString("htbh");
		String tzxm = params.optString("tzxm");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append("SELECT sum(TZJE) TZJE");
		sql.append("  FROM t_zhfw_cz_tzrxx");
		sql.append("   WHERE 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(TZSJ,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERID like "+ "'%"+username+"%'");
		}
		if(StringUtils.isNotEmpty(htbh)){
			sql.append(" AND HTBH like "+ "'%"+htbh+"%'");
		}
		if(StringUtils.isNotEmpty(tzxm)){
			sql.append(" AND LY_TYPE =:tzxm");
			paramMap.put("tzxm", tzxm);
		}
		sql.append(" order by ID desc LIMIT :START,:LIMIT");
		logger.info("queryQxtzjl sql is "+sql.toString());
		logger.info("queryQxtzjl paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(), paramMap, String.class);
	}
}
